﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace QuanLiNhaSachDAO
{
    public class TimKiemDAO
    {
        public static DataTable Tim(string theo, string noidung)
        {
            if (theo == "Sach") return TimSach(noidung);
            else
            {
                string sql = "SELECT * FROM " + theo + " WHERE id like '%" + noidung + "%' or Ten like N'%" + noidung + "%'";
                SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }

        public static DataTable TimSach(string noidung)
        {
            string sql = "";
            /*sql += "SELECT SACH.id AS [Mã Sách], ";
            sql += "SACH.Ten AS [Tên Sách], ";
            sql += "SACH.GiaMua AS [Giá mua/cuốn], ";
            sql += "SACH.GiaBan AS [Giá bán/cuốn], ";
            sql += "SACH.SoLuong AS [Số lượng], ";
            sql += "NHACUNGCAP.Ten AS [Tên Nhà Cung Cấp] ";
            sql += "FROM SACH INNER JOIN NHACUNGCAP ON SACH.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "WHERE SACH.id like '%" + noidung + "%' or SACH.Ten like N'%" + noidung + "%'";*/

            sql += "SELECT Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten AS NhaCungCap, Results.SoLuong, REPLACE(REPLACE(REPLACE((SELECT CAST(NHAXUATBAN.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHNHAXUATBAN INNER JOIN NHAXUATBAN ON SACHNHAXUATBAN.MaNhaXuatBan = NHAXUATBAN.id WHERE (SACHNHAXUATBAN.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS NhaXuatBan, REPLACE(REPLACE(REPLACE((SELECT CAST(THELOAI.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTHELOAI INNER JOIN THELOAI ON SACHTHELOAI.MaTheLoai = THELOAI.id WHERE (SACHTHELOAI.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TheLoai, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia ";
            sql += "FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETHOADON ON Results.id = CHITIETHOADON.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE Results.id like '%" + noidung + "%' or Results.Ten like N'%" + noidung + "%'";
            sql += "GROUP BY Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten, Results.SoLuong";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
         /*   dt.Columns.Add("Tác Giả");
            dt.Columns.Add("Thể Loại");
            dt.Columns.Add("Nhà xuất bản");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["Tác Giả"] = TacGiaDAO.TimTacGia(dt.Rows[i][0].ToString());
                dt.Rows[i]["Thể Loại"] = TheLoaiDAO.TimTheLoai(dt.Rows[i][0].ToString());
                dt.Rows[i]["Nhà xuất bản"] = NhaXuatBanDAO.TimNXB(dt.Rows[i][0].ToString());
            }
            */
            return dt;
        }
    }
}
